﻿using BrightIdeasSoftware;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using BO;
using System.Collections;
using UI.Core;
using System.Data.SqlClient;
namespace UI
{
    public partial class DepartmentYearReport : ReportFormBase,IMenuForm
    {
        public DepartmentYearReport()
        {
            InitializeComponent();
        }

        protected override void InitGrid()
        {

            MyGrid.Columns.AddRange(new OLVColumn[] { 
                new OLVColumn(FieldHelper.cnName.Year, "Year"),
                new OLVColumn(FieldHelper.cnName.Month, "Month"),
                new OLVColumn(FieldHelper.cnName.Company, "CompanyName"),
                new OLVColumn(FieldHelper.cnName.Center, "CenterName"),
                new OLVColumn(FieldHelper.cnName.Department, "DepartmentName"),
                new OLVColumn(FieldHelper.cnName.Amount, "Amount"),
                new OLVColumn("业绩目标", "TargetAmount"),
                new OLVColumn("达成率(%)", "Rate"),
            
            });


        }
        IEnumerable mySource;

        protected override IEnumerable MySource
        {
            get
            {
                return mySource;
            }
        }
        protected override void FetchData()
        {
            var timeNow = DateTime.Now;

            var sqlTxt = string.Format(
  @"Select b.CompanyName 
,b.CenterName
,b.DepartmentName
,b.Year,b.Month
,b.Amount 
,t.CompanyName as tName
,t.TargetAmount 
,Cast(Round(100*b.Amount /t.TargetAmount,2) as char)+'%' as Rate
from 
(
Select Billing.CompanyName
, Billing.CenterName
, Billing.DepartmentName
, YEAR(Billing.TransDate) as Year
, MONTH(Billing.TransDate) as Month
,Sum(Billing.Amount) as Amount
from Billing
Where YEAR(Billing.TransDate)='{0}'
and Billing.CompanyName<>''
and Billing.CenterName<>''
and Billing.DepartmentName<>''
and Billing.HasDeleted=0
and Billing.IsConfirm=1
Group By Billing.CompanyName,Billing.CenterName, Billing.DepartmentName,YEAR(Billing.TransDate),MONTH(Billing.TransDate)
) as b
Left Join
(
Select SaleTarget.CompanyName 
,SaleTarget.CenterName 
,SaleTarget.DepartmentName
, YEAR(SaleTarget.Month) as Year
, YEAR(SaleTarget.Month) as Month
,Sum(SaleTarget.Amount) as TargetAmount
from SaleTarget
Group By SaleTarget.CompanyName
,SaleTarget.CenterName 
,SaleTarget.DepartmentName
,YEAR(SaleTarget.Month)
,YEAR(SaleTarget.Month)
) as t
on b.CompanyName=t.CompanyName 
and b.CenterName=t.CenterName
and b.DepartmentName=t.DepartmentName
and b.Year=t.Year
and b.Month=t.Month
"
, timeNow
);
            var dt = BinDataTableAdapter.GetDataTable(CommandType.Text, sqlTxt);
            mySource = dt.DefaultView;

        }
    }
}
